 S Q L    domaca uloha                          *** Peter DROPPA ***
                                                *** 05.12.1996   ***


(1) ****************************************************************
create table pozical_si_pd
   ( datum date not null,
     citatel varchar2(30) not null,
     kniha varchar2(30) not null )
/
create table precital_pd
   ( citatel varchar2(30) not null,
     kniha varchar2(30) not null,
     datum date not null)
/
create table paci_sa_pd
   ( kniha varchar2(30) not null,
     citatel varchar2(30) not null )
/

insert into pozical_si_pd
values ('5-jan-96','Peter','Starec a more');
insert into pozical_si_pd
values ('15-jan-96','Miso','Marina');
insert into pozical_si_pd
values ('3-feb-96','Jurko','Tri gastanove kone');
insert into pozical_si_pd
values ('3-feb-96','Peter','Ostrovy v prude');
insert into pozical_si_pd
values ('5-feb-96','Miso','Ostrovy v prude');
insert into pozical_si_pd
values ('10-feb-96','Peter','Tri gastanove kone');
insert into pozical_si_pd
values ('7-mar-96','Jurko','Marina');
insert into pozical_si_pd
values ('11-mar-96','Peter','Ostrovy v prude');
insert into pozical_si_pd
values ('20-mar-96','Rado','Doktor Jesenius');

insert into precital_pd
values ('Miso','Marina','20-jan-96');
insert into precital_pd
values ('Peter','Starec a more','25-jan-96');
insert into precital_pd
values ('Jurko','Tri gastanove kone','5-feb-96');
insert into precital_pd
values ('Peter','Tri gastanove kone','28-feb-96');
insert into precital_pd
values ('Jurko','Marina','11-mar-96');

insert into paci_sa_pd
values ('Ostrovy v prude','Peter');
insert into paci_sa_pd
values ('Marina','Miso');
insert into paci_sa_pd
values ('Tri gastanove kone','Peter');
select distinct citatel from pozical_si_pd
minus
select citatel from precital_pd
/

(2) ***************************************************************
select distinct citatel from pozical_si_pd
where citatel not in
  (select citatel from precital_pd
  )
/

(3) ***************************************************************
select distinct citatel 
from pozical_si_pd
minus
(select citatel
 from precital_pd
 intersect
 select citatel
 from paci_sa_pd
)
/

(4) ***************************************************************
select distinct tab.kniha, citatel
from  pozical_si_pd tab
where citatel in
  (select citatel 
   from pozical_si_pd
   where kniha = tab.kniha
   group by citatel
   having count(kniha) =
     (select max(count(kniha))
      from pozical_si_pd 
      where kniha = tab.kniha
      group by citatel
     )
  )
/

(5) ***************************************************************
select playerno
from players
where leagueno is not null
/

(6) ***************************************************************
select playerno
from players
where year_joined between 1970 and 1980
/

(7) ***************************************************************
select name, playerno, leagueno
from players
where not (leagueno = 8467)
/

(8.1) *************************************************************
select teamno 
from teams t
where t.division = 'first'
      and
      t.playerno in
       (select playerno 
        from players
        where town = 'Stratford'
       )
/

Keby som chcel vypisat aj meno, tak by to slo tazko. V nasledujucom
pripade staci zmenit pry riadok na:
	select t.teamno, p.name
takto sa daju do vystupu lahko pridat aj ine informacie z tabulky players.

(8.2)
select t.teamno
from teams t, players p
where t.playerno = p.playerno
      and
      t.division = 'first'
      and
      p.town = 'Stratford'
/
